from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy import func
from backend.entities.load_data_new import LoadData
from backend.entities.weather_daily import WeatherDaily
from backend.config.database import get_db
from datetime import datetime, timedelta
from typing import List, Dict, Any
import logging

logger = logging.getLogger(__name__)

router = APIRouter(prefix="/dataset", tags=["数据集概览"])

@router.get("/overview")
async def get_dataset_overview(db: Session = Depends(get_db)):
    """获取数据集概览统计信息"""
    try:
        result = {}
        
        # 获取负荷数据统计
        load_stats = db.query(
            func.count(LoadData.id).label('total_records'),
            func.min(LoadData.dt).label('start_date'),
            func.max(LoadData.dt).label('end_date'),
            func.avg(LoadData.load_val).label('avg_load'),
            func.max(LoadData.load_val).label('max_load'),
            func.min(LoadData.load_val).label('min_load')
        ).first()
        
        if load_stats and load_stats.total_records > 0:
            # 计算时间跨度
            start_date = load_stats.start_date
            end_date = load_stats.end_date
            time_span_days = (end_date - start_date).days if start_date and end_date else 0
            
            result['load_data'] = {
                'total_records': load_stats.total_records,
                'start_date': start_date.strftime('%Y-%m-%d') if start_date else None,
                'end_date': end_date.strftime('%Y-%m-%d') if end_date else None,
                'time_span_days': time_span_days,
                'avg_load': round(float(load_stats.avg_load), 2) if load_stats.avg_load else 0,
                'max_load': round(float(load_stats.max_load), 2) if load_stats.max_load else 0,
                'min_load': round(float(load_stats.min_load), 2) if load_stats.min_load else 0
            }
        else:
            result['load_data'] = {
                'total_records': 0,
                'start_date': None,
                'end_date': None,
                'time_span_days': 0,
                'avg_load': 0,
                'max_load': 0,
                'min_load': 0
            }
        
        # 获取气象数据统计
        weather_stats = db.query(
            func.count(WeatherDaily.id).label('total_records'),
            func.min(WeatherDaily.dt).label('start_date'),
            func.max(WeatherDaily.dt).label('end_date'),
            func.avg(WeatherDaily.t_max).label('avg_temperature'),
            func.max(WeatherDaily.t_max).label('max_temperature'),
            func.min(WeatherDaily.t_min).label('min_temperature')
        ).first()
        
        if weather_stats and weather_stats.total_records > 0:
            # 计算时间跨度
            start_date = weather_stats.start_date
            end_date = weather_stats.end_date
            time_span_days = (end_date - start_date).days if start_date and end_date else 0
            
            result['weather_data'] = {
                'total_records': weather_stats.total_records,
                'start_date': start_date.strftime('%Y-%m-%d') if start_date else None,
                'end_date': end_date.strftime('%Y-%m-%d') if end_date else None,
                'time_span_days': time_span_days,
                'avg_temperature': round(float(weather_stats.avg_temperature), 2) if weather_stats.avg_temperature else 0,
                'max_temperature': round(float(weather_stats.max_temperature), 2) if weather_stats.max_temperature else 0,
                'min_temperature': round(float(weather_stats.min_temperature), 2) if weather_stats.min_temperature else 0
            }
        else:
            result['weather_data'] = {
                'total_records': 0,
                'start_date': None,
                'end_date': None,
                'time_span_days': 0,
                'avg_temperature': 0,
                'max_temperature': 0,
                'min_temperature': 0
            }
        
        # 获取最近30天的数据趋势
        thirty_days_ago = datetime.now() - timedelta(days=30)
        
        # 最近30天负荷数据趋势
        recent_load_trend = db.query(
            LoadData.dt,
            func.avg(LoadData.load_val).label('avg_load')
        ).filter(
            LoadData.dt >= thirty_days_ago
        ).group_by(LoadData.dt).order_by(LoadData.dt).all()
        
        # 最近30天气象数据趋势
        recent_weather_trend = db.query(
            WeatherDaily.dt,
            func.avg(WeatherDaily.t_max).label('avg_temperature')
        ).filter(
            WeatherDaily.dt >= thirty_days_ago
        ).group_by(WeatherDaily.dt).order_by(WeatherDaily.dt).all()
        
        result['trends'] = {
            'load_trend': [
                {
                    'date': item.dt.strftime('%Y-%m-%d'),
                    'value': round(float(item.avg_load), 2) if item.avg_load else 0
                } for item in recent_load_trend
            ],
            'temperature_trend': [
                {
                    'date': item.dt.strftime('%Y-%m-%d'),
                    'value': round(float(item.avg_temperature), 2) if item.avg_temperature else 0
                } for item in recent_weather_trend
            ]
        }
        
        # 检查前一天数据状态（用于训练的数据是截止到前一天）
        yesterday = datetime.now() - timedelta(days=1)
        yesterday_date = yesterday.date()
        
        # 检查前一天负荷数据
        yesterday_load_count = db.query(LoadData).filter(
            LoadData.dt == yesterday_date
        ).count()
        
        # 检查前一天气象数据
        yesterday_weather_count = db.query(WeatherDaily).filter(
            WeatherDaily.dt == yesterday_date
        ).count()
        
        # 数据完整性检查 - 只要有前一天的数据就算完整
        result['data_quality'] = {
            'load_data_status': 'complete' if yesterday_load_count > 0 else 'incomplete',
            'weather_data_status': 'complete' if yesterday_weather_count > 0 else 'incomplete',
            'last_updated': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            'yesterday_data': {
                'load_records': yesterday_load_count,
                'weather_records': yesterday_weather_count,
                'check_date': yesterday_date.strftime('%Y-%m-%d')
            }
        }
        
        return {
            "success": True,
            "message": "数据集概览获取成功",
            "data": result
        }
        
    except Exception as e:
        logger.error(f"获取数据集概览失败: {str(e)}")
        return {
            "success": False,
            "message": f"获取数据集概览失败: {str(e)}",
            "data": None
        }